<?php
namespace Home\Controller\SuperUser;
use Think\Controller;
/*
 * 超级管理员 2016.5.27
 * author:candy
 */

class UserController extends Controller {

    function _initialize(){
        // \Think\Hook::add('menu','Home\\Behaviors\\SuperUserBehavior');
    }

    public function _empty($name){
        $this->display("Event:404");      
    }


	/**
	 * 用户管理
     * 查看（搜索）用户信息，统计
     * 能不能删除用户？
     添加管理员
	 */
    public function index(){
    	$User = M('User');
    	$list = $User->select();
        $this->assign('list',$list);
        $this->display("SuperUser/SuperUser/index");
    }

    public function userlist(){
        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * Easy set variables
         */
         
        // DB table to use
        $table = 'user';
         
        // Table's primary key
        $primaryKey = 'user_id';
         
        // Array of database columns which should be read and sent back to DataTables.
        // The `db` parameter represents the column name in the database, while the `dt`
        // parameter represents the DataTables column identifier. In this case object
        // parameter names
        $columns = array(
            array( 'db' => 'user_id', 'dt' => 'user_id' ),
            array( 'db' => 'account',  'dt' => 'account' ),
            array( 'db' => 'name',   'dt' => 'name' ),
            array( 'db' => 'level',     'dt' => 'level' ),
            array( 'db' => 'exp',     'dt' => 'exp' ),
            array( 'db' => 'datetime',     'dt' => 'datetime' )
        );
         
        // SQL server connection information
        $sql_details = array(
            'user' => C('DB_USER'),
            'pass' => C('DB_PWD'),
            'db'   => C('DB_NAME'),
            'host' => C('DB_HOST')
        );

        if(I('get.startdate')&&I('get.enddate'))
            $where = "datetime between '".I('get.startdate')."' AND '".I('get.enddate')."'";
        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * If you just want to use the basic configuration for DataTables with PHP
         * server-side, there is no need to edit below this line.
         */
        Vendor('SSP');
        echo json_encode(
            \SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns,null,$where)
        );
    }

    public function add(){
        if(!IS_POST){
            $this->redirect("index");
        }else{
            $User = M('User');
            $User->create();
            date_default_timezone_set('PRC');   
            $createTime =  date('Y-m-d H:i:s');
            $User->datetime = $createTime;
            $User->name = $User->account;
            if($User->add()){
                $this->redirect("index");
            }else{
                $this->redirect("index");
            }
        }
    }


    public function checkUserProfile($userid=null){
        $Order = M("Order");
        $result = $Order
        ->join('order_item ON order.order_id = order_item.order_id')
        ->distinct(true)->group('order_item.order_item_id')
        ->join('item ON item.item_id = order_item.item_id','LEFT')
        ->join('good ON good.good_id = item.good_id','LEFT')
        ->join('picture ON picture.id = item.good_id','LEFT')->where('buyer ="'.$userid.'"')
        ->select();
        $this->assign('list',$result);
        $User = M('User');
        $user_result = $User->where('user_id="'.$userid.'"')->find();
        // dump($user_result);
        $this->assign('user',$user_result);
        $this->display("SuperUser/SuperUser/userprofile");
    }

    /**
     * 修改权限
     * 7.13
     */
    public function modifytype($id=null,$type=null){
        if($id&&$type){
            $newUser = D('User');
            $newUser->type = $type;
            if($newUser->where('user_id="'.$id.'"')->save()){

            }else{

            }
            redirect(U('Home/SuperUser/User/checkUserProfile/userid/'.$id));
        }
    }

    public function modify(){
        $User = M('User');
        $User->create();
        $User->save();
    }
    /**
     * 根据权限获取用户列表
     * 7.13
     */
    public function getUser($type=null){
        $user = M('User');
        $result = $user->where(array('type'=>$type))->select();
        return $result;
    }

    /**
     * 6.6
     * 批量删除用户
     */
    public function deleteUser(){

    }

    public function invoice(){
        $this->display("SuperUser/Invoice/invoice");
    }

    public function level($id=null){
        if($id){
            $user = D('User');

            // echo I('post.level');
            $user->level = I('post.level');
            if($user->where(array('user_id'=>$id))->save()){
                $this->success();
            }else{
                $this->error();
            }
            
        }
    }

    /**
     * 会员规则
     * 8.26
     */
    public function viprule(){
        $viprule = M('viprule');
        if(IS_POST){
            $rulelist = array();
            $Model = new \Think\Model();
            $Model->execute("TRUNCATE table `viprule`");
            for ($i=0; $i < count($_POST['rulename']); $i++) { 
                if($_POST['rulename'][$i]){
                    if($_POST['end'][$i]!=''){
                        $rulelist[] = array(
                                'rulename'=>$_POST['rulename'][$i],
                                'begin'=>$_POST['begin'][$i],
                                'end'=>$_POST['end'][$i],
                                'discount'=>$_POST['discount'][$i]
                            );
                    }else{
                        $rulelist[] = array(
                                'rulename'=>$_POST['rulename'][$i],
                                'begin'=>$_POST['begin'][$i],
                                'end'=>null,
                                'discount'=>$_POST['discount'][$i]
                            );
                    }

                }
            }
            $viprule->addAll($rulelist);
            // F('vip.rule',$rulelist);
            if(count($rulelist)>0)
                $this->assign('list',$rulelist);
            $this->display('SuperUser/SuperUser/vip');
        }else{
            // $rulelist = F('vip.rule');
            $rulelist = $viprule->select();
            if(count($rulelist)>0)
                $this->assign('list',$rulelist);
            $this->display('SuperUser/SuperUser/vip');
        }
    }

/**
 * 返回 等级名 
 * @param  [type] $assume [消费额]
 * @return [type] 'rulename' 区间 'begin' 'end' 优惠'discount' [等级名]
 */
    private function getrule($assume){
        // $rulelist = F('vip.rule');
        $viprule = M('viprule');
        $rulelist = $viprule->select();
        foreach ($rulelist as $key => $value) {
            if($value['end'] && $assume >= $value['begin'] && $assume <= $value['end']){
                return $value;
            }else if(!$value['end'] && $assume >= $value['begin']){
                return $value;
            }
        }
    }
/**
 * ajax返回 公共访问接口
 * @param  [type] $assume [description]
 * @return [type]         [description]
 */
    public function viplevel($assume){
        // $rulelist = F('vip.rule');
        $viprule = M('viprule');
        $rulelist = $viprule->select();
        foreach ($rulelist as $key => $value) {
            if($value['end'] && $assume >= $value['begin'] && $assume <= $value['end']){
                echo json_encode($value); 
            }else if(!$value['end'] && $assume >= $value['begin']){
                echo json_encode($value); 
            }
        }
    }

/**
 * 9.1
 * [countVip 会员统计]
 * @return [type] [description]
 */ 
    public function countVip(){
        $User = M('User');
        //1.总会员数
        $result['allvip'] = $User->where("type= 'normal'")->count();
        //2.会员充值数
        
        //3.活跃会员
        $start=I('post.start');
        $end = I('post.end');
        if($start&&$end){
          $map['datetime'] = array('between',array($start,$end));    
           $result['newvip'] = $User->where($map)->count();  
        }else{
          $result['newvip'] = 10;
        }
        //4.今日新增用户
        $result['activevip'] = 10; 
        echo json_encode($result);
        //5.当月新增用户
        //6.七天用户增长数
        //7.最近一个月用户数目变化
        //8.最近一年用户变化
    }

    /**
     * 统计日期区间的交易数
     * @return [type] [description]
     */
    public function lastuseres(){
      $Order = M('Order');
      $start=I('post.start');
      $end = I('post.end');

      if($start!=''&&$end!=''){
          $sql = "DROP PROCEDURE IF EXISTS make_intervals;
CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
-- *************************************************************************
-- Procedure: make_intervals()
--    Author: Ron Savage
--      Date: 02/03/2009
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
   declare thisDate timestamp;
   declare nextDate timestamp;
   set thisDate = startdate;

   -- *************************************************************************
   -- Drop / create the temp table
   -- *************************************************************************
   drop temporary table if exists time_intervals;
   create temporary table if not exists time_intervals
      (
      interval_start timestamp,
      interval_end timestamp
      );

   -- *************************************************************************
   -- Loop through the startdate adding each intval interval until enddate
   -- *************************************************************************
   repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;

      insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
      set thisDate = nextDate;
   until thisDate >= enddate
   end repeat;

 END;";

        $Order->execute($sql);
        $sql = "call make_intervals('".$start."','".$end."',1,'MONTH')";
        $Order->execute($sql);
        $result = $Order->query("select DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(interval_start)),'%y-%m') as Times,COUNT(user_id) as number from `user` RIGHT JOIN `time_intervals`
 ON DATE_FORMAT(datetime,'%y-%m')= DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(interval_start)),'%y-%m') where interval_start between '"
          .$start."' and '".$end."'  group by  DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(interval_start)),'%y-%m')");
        $new_result = array();
        foreach ($result as $key => $value) {
           $new_result[] = array_values($value);
        }
        echo json_encode($new_result);
      }
    }    

    /**
     * 数组转xls格式的excel文件
     * @param  array  $data      需要生成excel文件的数组
     * @param  string $filename  生成的excel文件名
     *      示例数据：
            $data = array(
                array(NULL, 2010, 2011, 2012),
                array('Q1',   12,   15,   21),
                array('Q2',   56,   73,   86),
                array('Q3',   52,   61,   69),
                array('Q4',   30,   32,    0),
               );
     */
    function create_xls($data,$filename='会员表.xls'){
        ini_set('max_execution_time', '0');
        Vendor('PHPExcel');
        $filename=str_replace('.xls', '', $filename).'.xls';
        $phpexcel = new \PHPExcel();
        $phpexcel->getProperties()
            ->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");
        $phpexcel->getActiveSheet()->fromArray($data);
        $phpexcel->getActiveSheet()->setTitle('Sheet1');
        $phpexcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=$filename");
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0
        $objwriter = \PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
        $objwriter->save('php://output');
        exit;
    }

    public function genxls(){
       /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * Easy set variables
         */
         
        // DB table to use
        $table = 'user';
         
        // Table's primary key
        $primaryKey = 'user_id';
         
        // Array of database columns which should be read and sent back to DataTables.
        // The `db` parameter represents the column name in the database, while the `dt`
        // parameter represents the DataTables column identifier. In this case object
        // parameter names
        $columns = array(
            array( 'db' => 'user_id', 'dt' => 'user_id' ),
            array( 'db' => 'account',  'dt' => 'account' ),
            array( 'db' => 'name',   'dt' => 'name' ),
            array( 'db' => 'level',     'dt' => 'level' ),
            // array( 'db' => 'exp',     'dt' => 'exp' ),
            array( 'db' => 'datetime',     'dt' => 'datetime' )
        );
         
        // SQL server connection information
        $sql_details = array(
            'user' => C('DB_USER'),
            'pass' => C('DB_PWD'),
            'db'   => C('DB_NAME'),
            'host' => C('DB_HOST')
        );

        if(I('get.startdate')&&I('get.enddate'))
            $where = "datetime between '".I('get.startdate')."' AND '".I('get.enddate')."'";
        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * If you just want to use the basic configuration for DataTables with PHP
         * server-side, there is no need to edit below this line.
         */
        Vendor('SSP');
        $title = array(array('编号','账号名','昵称','等级','注册日期'));
        $data = (\SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns,null,$where));
        $this->create_xls(array_merge($title,$data['data']));
    }


}